1 Public Class FrmPOSADD
2 Dim vat_tax As Double
3 Private Sub FrmPOSADD_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
4 'txtid.Text = ""
5 'txtname.Text = ""
6 'txtprice.Text = ""
7 'txtqty.Text = "0"
8 txtqty.Select()
9 vat_tax = (100 + VAT) / 100
10 'FrmPOSSTOCKSLIST.ShowDialog()
11 End Sub
12
13 Private Sub txtqty_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtqty.TextChanged
14 If txtqty.Text = "" Then txtqty.Text = "0"
15 txtqty.Text = str_Filter(txtqty, 48, 57, 0, 0)
16 With FrmPOS_CHOICES
17 If CDbl(txtqty.Text) > CDbl(txt_QTYi.Text) Then
18 If .rbdefect.Checked Then
19 MsgBox("Return quantity should not be greater than current quantity !!" & _
20 Chr(13) & "Return Quantity : " & txtqty.Text & _
21 Chr(13) & "Current Quantity : " & txt_QTYi.Text, MsgBoxStyle.Information, "Sales and Inventory")
22 txtqty.Text = 0
23 txtqty.Focus()
24 txtqty.Select()
25 Else
26 MsgBox("Order quantity should not be greater than current quantity !!" & _
27 Chr(13) & "Order Quantity : " & txtqty.Text & _
28 Chr(13) & "Current Quantity : " & txt_QTYi.Text, MsgBoxStyle.Information, "Sales and Inventory")
29 txtqty.Text = 0
30 txtqty.Focus()
31 txtqty.Select()
32 End If
33 End If
34 If CDbl(txtqty.Text) > 0 Then
35 cmdsave.Enabled = True
36 Else
37 cmdsave.Enabled = False
38 End If
39 End With
40 End Sub
41
42 Private Sub cmdcancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcancel.Click
43 Me.Close()
44 End Sub
45
46 Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click
47 Dim dtl_ID As Integer
48 Dim VATable As Double
49 Dim TotalAmount As Double
50 Dim TotalSale As Double
51 Dim Vat12 As Double
52 Dim TotalAmount_Due As Double
53 Dim tmpSalesID As Integer
54
55 VATable = 0
56 TotalAmount = 0
57 TotalSale = 0
58 Vat12 = 0
59 TotalAmount_Due = 0
60
61 With FrmPOS_CHOICES
62 If CDbl(txtqty.Text) > 0 Then
63 If .rbdefect.Checked Then
64
65
66
67 sqlSTR = "SELECT * FROM TBL_Pending_Item WHERE Pending_ID =" & txtpendingID.Text
68 ExecuteSQLQuery(sqlSTR)
69 If sqlDT.Rows.Count > 0 Then
70 dtl_ID = sqlDT.Rows(0)("sales_Detail_ID")
71 'FOR ITEM_QTY
72 'sqlSTR = "SELECT * FROM TBL_Sales_Sold_Detail WHERE Sales_Detail_ID =" & dtl_ID & " AND Sales_ID =" & FrmPOSVIEW.txtreceiptid.Text
73 sqlSTR = "SELECT * FROM TBL_Sales_Receipt " & _
74 "INNER JOIN TBL_Sales_Sold_Detail ON TBL_Sales_Receipt.Sales_ID = TBL_Sales_Sold_Detail.Sales_ID " & _
75 " WHERE TBL_Sales_Receipt.Receipt_ID =" & FrmPOSVIEW.txtreceiptid.Text & _
76 " AND Sales_Detail_ID =" & dtl_ID
77 ExecuteSQLQuery(sqlSTR)
78
79 'GET SALES_ID
80 tmpSalesID = sqlDT.Rows(0)("Sales_ID")
81
82 sqlSTR = "UPDATE TBL_Sales_Sold_Detail " & _
83 "SET Item_QTY = Item_QTY + " & CDbl(txtqty.Text) & ", " _
84 & "Total_Price = " & CDbl((CDbl(sqlDT.Rows(0)("Item_QTY")) + CDbl(txtqty.Text)) * CDbl(txtprice.Text)) & ", " _
85 & "Item_Name ='" & R_eplace(txtname.Text) & " / " & CDbl(txtqty.Text) & " QTY Return Product(s)" & "', " _
86 & "Added_QTY = Added_QTY + " & CDbl(txtqty.Text) & _
87 " WHERE Sales_Detail_ID =" & dtl_ID & _
88 " AND Sales_ID =" & tmpSalesID
89 ExecuteSQLQuery(sqlSTR)
90
91 'START TO RE-COMPUTE
92 sqlSTR = "SELECT * FROM TBL_Sales_Sold_Detail WHERE Sales_ID =" & tmpSalesID
93 ExecuteSQLQuery(sqlSTR)
94 If sqlDT.Rows.Count > 0 Then
95 For x = 0 To sqlDT.Rows.Count - 1
96 TotalAmount = TotalAmount + CDbl(sqlDT.Rows(x)("Item_QTY")) * CDbl(sqlDT.Rows(x)("Item_Price"))
97 Next
98 End If
99
100 VATable = Format(TotalAmount - (TotalAmount - (TotalAmount / vat_tax)), "###,###.00")
101 TotalSale = VATable
102 Vat12 = Format((TotalAmount - (TotalAmount / vat_tax)), "###,###.00")
103 TotalAmount_Due = Format(CDbl(TotalSale) + CDbl(Vat12), "###,###,###.00")
104
105 'FOR TBL_Sales_Sold
106 sqlSTR = "UPDATE TBL_Sales_Sold " & _
107 "SET Amount_Due =" & TotalAmount & ", " _
108 & "Amount_Change = Amount_Receive -" & TotalAmount & _
109 " WHERE Sales_ID =" & tmpSalesID
110 ExecuteSQLQuery(sqlSTR)
111
112 'FOR TBL_Sales_Receipt
113 sqlSTR = "UPDATE TBL_Sales_Receipt " & _
114 "SET Vatable =" & VATable & ", " & "Total_Sale =" & TotalSale & ", " & _
115 "Amount_Due =" & TotalAmount_Due & ", " & "Vat =" & Vat12 & _
116 "WHERE Sales_ID =" & tmpSalesID
117 ExecuteSQLQuery(sqlSTR)
118
119 'UPDATE STOCKS
120 sqlSTR = "UPDATE TBL_Stocks_Balances " & _
121 "SET Item_QTY = Item_QTY - " & CDbl(txtqty.Text) & _
122 "WHERE Item_ID =" & txtid.Text
123 ExecuteSQLQuery(sqlSTR)
124 End If
125 'Product Return
126 If CDbl(txtqty.Text) = CDbl(txt_QTYi.Text) Then
127 sqlSTR = "UPDATE TBL_Pending_Item SET Returnx= 'Yes'" & ", " & _
128 "Return_Date ='" & Format(Now, "MM/dd/yyyy") & "'" & _
129 " WHERE Pending_ID =" & txtpendingID.Text
130 ExecuteSQLQuery(sqlSTR)
131 '-----
132 sqlSTR = "UPDATE TBL_Sales_Sold_Detail " & _
133 "SET Added_QTY = 0 WHERE Sales_Detail_ID =" & dtl_ID & _
134 " AND Sales_ID =" & tmpSalesID
135 ExecuteSQLQuery(sqlSTR)
136 End If
137 Audit_Trail(xUser_ID, TimeOfDay, "Return defect item to customer")
138 ElseIf .rbNew.Checked Then
139 'ADD NEW PRODUCTx
140 sqlSTR = "SELECT * FROM TBL_Sales_Receipt WHERE Receipt_ID =" & FrmPOSVIEW.txtreceiptid.Text
141 ExecuteSQLQuery(sqlSTR)
142 tmpSalesID = sqlDT.Rows(0)("Sales_ID")
143 'GET SALES_ID
144
145 sqlSTR = "INSERT INTO TBL_Sales_Sold_Detail (Sales_ID, Item_ID, Item_Name, Item_Description, Item_Price, Item_Qty, Total_Price, Added_QTY) " & _
146 "VALUES (" & tmpSalesID & ", " _
147 & txtid.Text & ", " _
148 & "'" & R_eplace(txtname.Text) & " [ Added new product ]" & "', " _
149 & "'" & R_eplace(txtdesc.Text) & "', " _
150 & CDbl(txtprice.Text) & ", " _
151 & CDbl(txtqty.Text) & ", " _
152 & CDbl(txtprice.Text) * CDbl(txtqty.Text) & ", " _
153 & 0 & ")"
154 ExecuteSQLQuery(sqlSTR)
155
156 'START TO RE-COMPUTE
157 sqlSTR = "SELECT * FROM TBL_Sales_Sold_Detail WHERE Sales_ID =" & tmpSalesID
158 ExecuteSQLQuery(sqlSTR)
159 If sqlDT.Rows.Count > 0 Then
160 For x = 0 To sqlDT.Rows.Count - 1
161 TotalAmount = TotalAmount + CDbl(sqlDT.Rows(x)("Item_QTY")) * CDbl(sqlDT.Rows(x)("Item_Price"))
162 Next
163 End If
164
165 VATable = Format(TotalAmount - (TotalAmount - (TotalAmount / vat_tax)), "###,###.00")
166 TotalSale = VATable
167 Vat12 = Format((TotalAmount - (TotalAmount / vat_tax)), "###,###.00")
168 TotalAmount_Due = Format(CDbl(TotalSale) + CDbl(Vat12), "###,###,###.00")
169
170 'FOR TBL_Sales_Sold
171 sqlSTR = "UPDATE TBL_Sales_Sold " & _
172 "SET Amount_Due =" & TotalAmount & ", " _
173 & "Amount_Change = Amount_Receive -" & TotalAmount & _
174 " WHERE Sales_ID =" & tmpSalesID
175 ExecuteSQLQuery(sqlSTR)
176
177 'FOR TBL_Sales_Receipt
178 sqlSTR = "UPDATE TBL_Sales_Receipt " & _
179 "SET Vatable =" & VATable & ", " & "Total_Sale =" & TotalSale & ", " & _
180 "Amount_Due =" & TotalAmount_Due & ", " & "Vat =" & Vat12 & _
181 "WHERE Receipt_ID =" & FrmPOSVIEW.txtreceiptid.Text
182 ExecuteSQLQuery(sqlSTR)
183
184 'UPDATE STOCKS
185 sqlSTR = "UPDATE TBL_Stocks_Balances " & _
186 "SET Item_QTY = Item_QTY - " & CDbl(txtqty.Text) & _
187 "WHERE Item_ID =" & txtid.Text
188 ExecuteSQLQuery(sqlSTR)
189
190 Audit_Trail(xUser_ID, TimeOfDay, "Add New Stocks to Customer")
191 End If
192 End If
193
194 End With
195
196 Me.Close()
197 End Sub
198 End Class
